﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;

namespace XclusiveService.Models
{
    public class DatabaseManager
    {
        /*
         *************************************
         * XCLUSIVE SHIPMENT ORDERS' C.R.U.D.
         *************************************
        */

        // Get All Orders
        public static ArrayList GetAllOrder()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["XLConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM [Xclusive].[dbo].[Order]";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Order o = new Order();
                    o.orderID = (int)dr["orderID"];
                    o.status = (string)dr["status"];
                    o.ship_date = (string)dr["ship_date"];
                    o.ship_time = (string)dr["ship_time"];
                    o.ship_type = (string)dr["ship_type"];
                    o.address = (string)dr["address"];
                    o.employeeID = (string)dr["employeeID"];
                    o.listID = (int)dr["listID"];
                    result.Add(o);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        // Get Order By ID
        public static Order GetOrderById(int id)
        {
            Order o = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["XLConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM [Xclusive].[dbo].[Order] WHERE orderID=@orderID";
                comm.Parameters.AddWithValue("@orderID", id);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    o = new Order();
                    o.orderID = (int)dr["orderID"];
                    o.status = (string)dr["status"];
                    o.ship_date = (string)dr["ship_date"];
                    o.ship_time = (string)dr["ship_time"];
                    o.ship_type = (string)dr["ship_type"];
                    o.address = (string)dr["address"];
                    o.employeeID = (string)dr["employeeID"];
                    o.listID = (int)dr["listID"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return o;

        }

        // Add an Order
        public static int InsertOrder(Order o)
        {
            int rowsinserted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["XLConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO [Xclusive].[dbo].[Order](status,ship_date,ship_time,ship_type,address,employeeID,listID)" +
                    " VALUES (@status,@ship_date,@ship_time,@ship_type,@address,@employeeID,@listID)";
                comm.Parameters.AddWithValue("@status", o.status);
                comm.Parameters.AddWithValue("@ship_date", o.ship_date);
                comm.Parameters.AddWithValue("@ship_time", o.ship_time);
                comm.Parameters.AddWithValue("@ship_type", o.ship_type);
                comm.Parameters.AddWithValue("@address", o.address);
                comm.Parameters.AddWithValue("@employeeID", o.employeeID);
                comm.Parameters.AddWithValue("@listID", o.listID);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }

        // Update an Order
        public static int UpdateOrder(Order o)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["XLConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE [Xclusive].[dbo].[Order] SET status=@status,ship_date=@ship_date,ship_time=@ship_time,ship_type=@ship_type,address=@address,employeeID=@employeeID where orderID=@orderID";
                comm.Parameters.AddWithValue("@status", o.status);
                comm.Parameters.AddWithValue("@ship_date", o.ship_date);
                comm.Parameters.AddWithValue("@ship_time", o.ship_time);
                comm.Parameters.AddWithValue("@ship_type", o.ship_type);
                comm.Parameters.AddWithValue("@address", o.address);
                comm.Parameters.AddWithValue("@employeeID", o.employeeID);
                comm.Parameters.AddWithValue("@orderID", o.orderID);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        // Delete an Order
        public static int DeleteOrder(int orderID)
        {
            int rowsdeleted = 0;
            int newSeedValue = (orderID - 1);

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["XLConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM [Xclusive].[dbo].[Order] where orderID=@orderID;";
                comm.CommandText += "DBCC CHECKIDENT ('Xclusive.dbo.Order', RESEED, " + newSeedValue +")";
                comm.Parameters.AddWithValue("@orderID", orderID);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }

        /*
         ***********************************************
         * XCLUSIVE SHIPMENT ORDER ITEM LISTS' C.R.U.D.
         ***********************************************
        */

        public static ArrayList GetAllItemList()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["XLConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM [Xclusive].[dbo].[ItemList]";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    ItemList l = new ItemList();
                    l.listID = (int)dr["listID"];
                    l.productID = (int)dr["productID"];
                    l.item_name = (string)dr["item_name"];
                    l.qty = (int)dr["qty"];
                    l.price = Convert.ToDouble((decimal)dr["price"]);
                    result.Add(l);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        // Get ItemList By ID
        public static ItemList GetItemListById(int id)
        {
            ItemList l = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["XLConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM [Xclusive].[dbo].[ItemList] WHERE listID=@listID";
                comm.Parameters.AddWithValue("@listID", id);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    l = new ItemList();
                    l.listID = (int)dr["listID"];
                    l.productID = (int)dr["productID"];
                    l.item_name = (string)dr["item_name"];
                    l.qty = (int)dr["qty"];
                    l.price = Convert.ToDouble((decimal)dr["price"]);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return l;

        }

        // Add an ItemList
        public static int InsertItemList(ItemList l)
        {
            int rowsinserted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["XLConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO [Xclusive].[dbo].[ItemList](productID,item_name,qty,price)" +
                    " VALUES (@productID,@item_name,@qty,@price)";
                comm.Parameters.AddWithValue("@productID", l.productID);
                comm.Parameters.AddWithValue("@item_name", l.item_name);
                comm.Parameters.AddWithValue("@qty", l.qty);
                comm.Parameters.AddWithValue("@price", l.price);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }

        // Update an ItemList
        public static int UpdateItemList(ItemList l)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["XLConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE [Xclusive].[dbo].[ItemList] SET productID=@productID,item_name=@item_name,qty=@qty,price=@price where listID=@listID";
                comm.Parameters.AddWithValue("@productID", l.productID);
                comm.Parameters.AddWithValue("@item_name", l.item_name);
                comm.Parameters.AddWithValue("@qty", l.qty);
                comm.Parameters.AddWithValue("@price", l.price);
                comm.Parameters.AddWithValue("@listID", l.listID);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }

        // Delete an ItemList
        public static int DeleteItemList(int listID)
        {
            int rowsdeleted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["XLConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM [Xclusive].[dbo].[ItemList] where listID=@listID";
                comm.Parameters.AddWithValue("@listID", listID);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        }

        /*
         *************************************
         * XCLUSIVE CLIENT' C.R.U.D.
         *************************************
        */

        //Getting all the Clients
        public static ArrayList GetAllClient()
        {
            ArrayList client = new ArrayList();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["XLConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM [Xclusive].[dbo].[Client]";
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Client c = new Client();
                    c.username = (string)dr["username"];
                    c.full_name = (string)dr["full_name"];
                    c.password = (string)dr["password"];
                    c.email = (string)dr["email"];
                    c.address = (string)dr["address"];
                    c.contact_number = (int)dr["contact_number"];
                    c.company_country = (string)dr["company_country"];
                    c.company_name = (string)dr["company_name"];
                    c.company_email = (string)dr["company_email"];
                    c.company_address = (string)dr["company_address"];
                    c.account_type = (string)dr["account_type"];
                    client.Add(c);
                }

                dr.Close();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return client;
        }

        //Getting a Client by company_name
        public static Client GetClientByCompanyName(string company_name)
        {
            Client c = null;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["XLConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM [Xclusive].[dbo].[Client] WHERE company_name=@company_name";
                comm.Parameters.AddWithValue("@company_name", company_name);
                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    c = new Client();
                    c.username = (string)dr["username"];
                    c.full_name = (string)dr["full_name"];
                    c.password = (string)dr["password"];
                    c.email = (string)dr["email"];
                    c.address = (string)dr["address"];
                    c.contact_number = (int)dr["contact_number"];
                    c.company_country = (string)dr["company_country"];
                    c.company_name = (string)dr["company_name"];
                    c.company_email = (string)dr["company_email"];
                    c.company_address = (string)dr["company_address"];
                    c.account_type = (string)dr["account_type"];
                }

                dr.Close();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return c;
        }

        //Creating a Client
        public static int CreateClient(Client c)
        {
            int rowsAffected = 0;

            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["XLConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO [Xclusive].[dbo].[Client](username,full_name,password,email,address,contact_number,company_country,company_name,company_email,company_address,account_type) VALUES (@username,@full_name,@password,@email,@address,@contact_number,@company_country,@company_name,@company_email,@company_address,@account_type)";
                comm.Parameters.AddWithValue("@username", c.username);
                comm.Parameters.AddWithValue("@full_name", c.full_name);
                comm.Parameters.AddWithValue("@password", c.password);
                comm.Parameters.AddWithValue("@email", c.email);
                comm.Parameters.AddWithValue("@address", c.address);
                comm.Parameters.AddWithValue("@contact_number", c.contact_number);
                comm.Parameters.AddWithValue("@company_country", c.company_country);
                comm.Parameters.AddWithValue("@company_name", c.company_name);
                comm.Parameters.AddWithValue("@company_email", c.company_email);
                comm.Parameters.AddWithValue("@company_address", c.company_address);
                comm.Parameters.AddWithValue("@account_type", c.account_type);

                rowsAffected = comm.ExecuteNonQuery();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return rowsAffected;
        }

        //Update a Client
        public static int UpdateClient(Client c)
        {
            int rowsUpdated = 0;

            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["XLConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE [Xclusive].[dbo].[Client] SET username=@username,full_name=@full_name,password=@password,email=@email,address=@address,contact_number=@contact_number,company_country=@company_country,company_email=@company_email,company_address=@company_address WHERE company_name=@company_name";
                comm.Parameters.AddWithValue("@username", c.username);
                comm.Parameters.AddWithValue("@full_name", c.full_name);
                comm.Parameters.AddWithValue("@password", c.password);
                comm.Parameters.AddWithValue("@email", c.email);
                comm.Parameters.AddWithValue("@address", c.address);
                comm.Parameters.AddWithValue("@contact_number", c.contact_number);
                comm.Parameters.AddWithValue("@company_country", c.company_country);
                comm.Parameters.AddWithValue("@company_email", c.company_email);
                comm.Parameters.AddWithValue("@company_address", c.company_address);
                comm.Parameters.AddWithValue("@company_name", c.company_name);

                rowsUpdated = comm.ExecuteNonQuery();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return rowsUpdated;
        }

        //Deleting a Client
        public static int DeleteClient(string company_name)
        {
            int rowsDeleted = 0;

            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["XLConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM [Xclusive].[dbo].[Client] WHERE company_name=@company_name";
                comm.Parameters.AddWithValue("@company_name", company_name);

                rowsDeleted = comm.ExecuteNonQuery();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return rowsDeleted;
        }

        /*
         *************************************
         * XCLUSIVE EMPLOYEE' C.R.U.D.
         *************************************
        */

        //Getting all the Employees
        public static ArrayList GetAllEmployees()
        {
            ArrayList employee = new ArrayList();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["XLConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM [Xclusive].[dbo].[Employee]";
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Employee emp = new Employee();
                    emp.employeeID = (int)dr["employeeID"];
                    emp.name = (string)dr["name"];
                    emp.company_name = (string)dr["company_name"];
                    emp.account_type = (string)dr["account_type"];
                    employee.Add(emp);
                }

                dr.Close();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return employee;
        }

        //Getting the Employees by company_name
        public static ArrayList GetEmployeesByCompanyName(string company_name)
        {
            ArrayList employee = new ArrayList();
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["XLConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM [Xclusive].[dbo].[Employee] WHERE company_name=@company_name";
                comm.Parameters.AddWithValue("@company_name", company_name);
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Employee emp = new Employee();
                    emp.employeeID = (int)dr["employeeID"];
                    emp.name = (string)dr["name"];
                    emp.company_name = (string)dr["company_name"];
                    emp.account_type = (string)dr["account_type"];
                    employee.Add(emp);
                }

                dr.Close();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return employee;
        }

        //Getting an Employee by employeeID
        public static Employee GetEmployeeByEmployeeID(int employeeID)
        {
            Employee emp = null;
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["XLConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM [Xclusive].[dbo].[Employee] WHERE employeeID=@employeeID";
                comm.Parameters.AddWithValue("@employeeID", employeeID);
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    emp = new Employee();
                    emp.employeeID = (int)dr["employeeID"];
                    emp.name = (string)dr["name"];
                    emp.company_name = (string)dr["company_name"];
                    emp.account_type = (string)dr["account_type"];
                }

                dr.Close();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return emp;
        }

        //Creating an Employee
        public static int CreateEmployee(Employee emp)
        {
            int rowsAffected = 0;

            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["XLConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO [Xclusive].[dbo].[Employee](name,company_name,password,account_type) VALUES (@name,@company_name,@password,@account_type)";
                comm.Parameters.AddWithValue("@name", emp.name);
                comm.Parameters.AddWithValue("@company_name", emp.company_name);
                comm.Parameters.AddWithValue("@password", emp.password);
                comm.Parameters.AddWithValue("@account_type", emp.account_type);

                rowsAffected = comm.ExecuteNonQuery();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return rowsAffected;
        }

        //Updating an Employee
        public static int UpdateEmployee(Employee emp)
        {
            int rowsUpdated = 0;

            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["XLConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE [Xclusive].[dbo].[Employee] SET name=@name,password=@password WHERE employeeID=@employeeID";
                comm.Parameters.AddWithValue("@name", emp.name);
                comm.Parameters.AddWithValue("@password", emp.password);
                comm.Parameters.AddWithValue("@employeeID", emp.employeeID);

                rowsUpdated = comm.ExecuteNonQuery();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return rowsUpdated;
        }

        //Deleting an Employee by EmployeeID
        public static int DeleteEmployeeByEmployeeID(int employeeID)
        {
            int rowsDeleted = 0;

            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["XLConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM [Xclusive].[dbo].[Employee] WHERE employeeID=@employeeID";
                comm.Parameters.AddWithValue("@employeeID", employeeID);

                rowsDeleted = comm.ExecuteNonQuery();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return rowsDeleted;
        }

        //Deleting the employees by company_name
        public static int DeleteEmployeeByCompanyName(int company_name)
        {
            int rowsDeleted = 0;

            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["XLConnString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM [Xclusive].[dbo].[Employee] WHERE company_name=@company_name";
                comm.Parameters.AddWithValue("@company_name", company_name);

                rowsDeleted = comm.ExecuteNonQuery();
                conn.Close();
            }

            catch (SqlException e)
            {
                throw e;
            }

            return rowsDeleted;
        }
    }
}